IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_DistributionInspection_ProjectSummary_Budget')
	BEGIN
		DROP  Procedure  up_Report_DistributionInspection_ProjectSummary_Budget
	END

GO

CREATE Procedure up_Report_DistributionInspection_ProjectSummary_Budget
	@ProjectId int = NULL
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SELECT  @ProjectId = CONVERT(INT, SettingValue) FROM tb_Settings
	WHERE SettingKey like 'DistributionInspectionProjectId'
END

--Get Estimated Counts and prices
DECLARE @EstimatedPartial int
DECLARE @EstimatedVisual int
DECLARE @EstimatedFull int
DECLARE @EstimatedGuyGuards int
DECLARE @EstimatedGroundWire int
DECLARE @EstimatedPoleTag int
DECLARE @EstimatedTransformer int
DECLARE @EstimatedSectionalizer int
DECLARE @EstimatedRecloser int

DECLARE @ParcialPrice MONEY
DECLARE @VisualPrice MONEY
DECLARE @FullPrice MONEY
DECLARE @GuyGuardsPrice MONEY
DECLARE @GroundWirePrice MONEY
DECLARE @PoleTagPrice MONEY
DECLARE @TransformerPrice MONEY
DECLARE @SectionalizerPrice MONEY
DECLARE @RecloserPrice MONEY

SELECT 
	 @EstimatedPartial = EstimatedPartialCount
	,@EstimatedVisual = EstimatedVisualCount
	,@EstimatedFull = EstimatedFullCount
	,@EstimatedGuyGuards = EstimatedGuyGuardsCount
	,@EstimatedGroundWire = EstimatedGroundWireCount
	,@EstimatedPoleTag = EstimatedPoleTagCount
	,@EstimatedTransformer = EstimatedTransformerCount 
	,@EstimatedSectionalizer = EstimatedSectionalizerCount 
	,@EstimatedRecloser = EstimatedRecloserCount 
	,@ParcialPrice = PartialUnitPrice
	,@VisualPrice = VisualUnitPrice
	,@FullPrice = FullUnitPrice
	,@GuyGuardsPrice = GuyGuardsUnitPrice
	,@GroundWirePrice = GroundWireUnitPrice
	,@PoleTagPrice =PoleTagUnitPrice
	,@TransformerPrice = TransformerUnitPrice
	,@SectionalizerPrice = SectionalizerUnitPrice
	,@RecloserPrice = RecloserUnitPrice
FROM	
	tb_vendor_projects
WHERE 
	Vendor_ProjectId = @ProjectId
/*
select *
from tb_vendor_projects
*/


--Get Visual and Partial Results Values
SELECT
	T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId AS 'Order',
	T.Name,
	Count(R.Vendor_Distribution_TypeFK) as InspectionAmounts ,
	R.Vendor_Distribution_TypeFK as InspectionTypeFk,
	@ProjectId as ProjectId,
	ISNULL((CASE R.Vendor_Distribution_TypeFK
		WHEN  1 THEN (@EstimatedFull)
		WHEN  2 THEN (@EstimatedPartial)
		WHEN  3 THEN (@EstimatedVisual)
	END),0) AS Estimated,
	(CASE R.Vendor_Distribution_TypeFK
		WHEN  1 THEN CAST(@FullPrice as nvarchar) 
		WHEN  2 THEN CAST(@ParcialPrice as nvarchar) 
		WHEN  3 THEN CAST(@VisualPrice as nvarchar) 
	END) AS UnityValue,
	ISNULL((CASE R.Vendor_Distribution_TypeFK
		WHEN  1 THEN COUNT(R.Vendor_Distribution_TypeFK) * @FullPrice
		WHEN  2 THEN COUNT(R.Vendor_Distribution_TypeFK) * @ParcialPrice
		WHEN  3 THEN COUNT(R.Vendor_Distribution_TypeFK) * @VisualPrice
	END),0) AS subTotal,
	ISNULL((CASE R.Vendor_Distribution_TypeFK
		WHEN  1 THEN @EstimatedFull * @FullPrice
		WHEN  2 THEN @EstimatedPartial * @ParcialPrice
		WHEN  3 THEN @EstimatedVisual * @VisualPrice
	END),0) AS subTotalEstimated
FROM 
	tb_Vendor_Distribution_Poles P
INNER JOIN tb_Vendor_Distribution_InspectionResults R 
	ON R.Vendor_Distribution_PoleFk = P.Vendor_Distribution_Poles_PoleId
INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Inspection_Type T 
	ON R.Vendor_Distribution_TypeFK = T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId
WHERE
	P.ProjectFk = @ProjectId
GROUP BY 
	T.Vendor_InspVisit_XmissionStructures_Inspection_TypeId, R.Vendor_Distribution_TypeFK, T.Name

UNION

--Gets Ground Wire results
SELECT
	4 AS 'Order' 	
	,'Ground Wire Repair' as 'Name'
	,COUNT(*) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedGroundWire  AS 'Estimated'
	,@GroundWirePrice AS 'UnityValue'
	,COUNT(*) * @GroundWirePrice AS 'SubTotal'
	,@EstimatedGroundWire * @GroundWirePrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
WHERE 
	P.ProjectFk = @ProjectId
	AND P.GroundWireRepair = 1
	AND P.DistributionType = 1 --Inspection

UNION

--Gets Guy Guard Results
SELECT
	5 AS 'Order' 	
	,'Guy Guard' as 'Name'
	,SUM(GuardsQuantity) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedGuyGuards AS 'Estimated'
	,@GuyGuardsPrice AS 'UnityValue'
	,SUM(GuardsQuantity) * @GuyGuardsPrice AS 'SubTotal'
	,@EstimatedGuyGuards * @GuyGuardsPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
WHERE 
	P.ProjectFk = @ProjectId
	AND P.DistributionType = 1 --Inspection
	AND P.Billable = 1

UNION

--Gets Pole Tag results
SELECT
	6 AS 'Order' 	
	,'Pole Tag Id' as 'Name'
	,COUNT(*) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedPoleTag AS 'Estimated'
	,@PoleTagPrice AS 'UnityValue'
	,COUNT(*) * @PoleTagPrice AS 'SubTotal'
	,@EstimatedPoleTag * @PoleTagPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
WHERE 
	P.ProjectFk = @ProjectId
	AND P.DistributionType = 1 --Inspection
	AND P.PoleTag = 1

UNION

--Gets Transformers Results
SELECT
	7 AS 'Order' 	
	,'Transformers' as 'Name'
	,COUNT(T.Vendor_Distribution_PoleFk) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedTransformer AS 'Estimated'
	,@TransformerPrice AS 'UnityValue'
	,COUNT(T.Vendor_Distribution_PoleFk) * @TransformerPrice  AS 'SubTotal'
	,@EstimatedTransformer * @TransformerPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
INNER JOIN tb_Vendor_Distribution_Transformers T
	ON P.Vendor_Distribution_Poles_PoleId = T.Vendor_Distribution_PoleFk
WHERE
	P.ProjectFk = @ProjectId
	AND P.DistributionType = 1 --Inspection

UNION

--Gets Sectionalizer Results
SELECT
	8 AS 'Order' 	
	,'Sectionalizers' as 'Name'
	,COUNT(S.Vendor_Distribution_PoleFk) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedSectionalizer AS 'Estimated'
	,@SectionalizerPrice AS 'UnityValue'
	,COUNT(S.Vendor_Distribution_PoleFk) * @SectionalizerPrice  AS 'SubTotal'
	,@EstimatedSectionalizer * @SectionalizerPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
INNER JOIN tb_Vendor_Distribution_Sectionalizers S
	ON P.Vendor_Distribution_Poles_PoleId = S.Vendor_Distribution_PoleFk
WHERE
	P.ProjectFk = @ProjectId
	AND P.DistributionType = 1 --Inspection

UNION

--Gets Recloser Results
SELECT
	9 AS 'Order' 	
	,'Reclosers' as 'Name'
	,COUNT(R.Vendor_Distribution_PoleFk) 'InspectionAmounts'
	,NULL AS InspectionTypeFk
	,@ProjectId as 'ProjectId'
	,@EstimatedRecloser AS 'Estimated'
	,@RecloserPrice AS 'UnityValue'
	,COUNT(R.Vendor_Distribution_PoleFk) * @RecloserPrice  AS 'SubTotal'
	,@EstimatedRecloser * @RecloserPrice AS 'SubTotalEstimated'
FROM 
	tb_Vendor_Distribution_Poles P
INNER JOIN tb_Vendor_Distribution_Reclosers R
	ON P.Vendor_Distribution_Poles_PoleId = R.Vendor_Distribution_PoleFk
WHERE
	P.ProjectFk = @ProjectId
	AND P.DistributionType = 1 --Inspection

ORDER BY 1

